import pymysql


def OpenMysql():
    print("进入打开数据库函数")

    db = pymysql.connect(host="localhost", user="root", password="root", database="sys")

    curses = db.cursor()

    curses.execute("select version()")

    data = curses.fetchone()

    print(data)

    db.close()


def createTable():
    db = pymysql.connect(host="localhost", user="root", password="root", database="sys")

    curses = db.cursor()
    curses.execute("drop table if exists employee")
    sql = """create table employee(
          id int primary key,
          firstname char(20) not null,
          age int)
          """

    curses.execute(sql)
    db.close()

def insertEmp():
    db = pymysql.connect(host="localhost", user="root", password="root", database="sys")
    curses = db.cursor()
    sql = "insert into employee(id, firstname, age) values(2, 'bb', 12)"
    try:
        curses.execute(sql)
        db.commit()
    except:
        db.rollback()
    db.close()

def selectEmp():
    db = pymysql.connect(host="localhost", user="root", password="root", database="sys")
    curses = db.cursor()
    sql = "select * from employee"
    try:
        curses.execute(sql)
        res = curses.fetchall()
        for row in res:
            id = res[0]
            name = row[1]
            age = row[2]
            print("id=%s, name=%s, age=%s" % (id, name, age))
    except:
        print("读取数据失败")
    db.close()

def updateEmp(myId):
    print(myId)
    db = pymysql.connect(host="localhost", user="root", password="root", database="sys")
    curses = db.cursor()
    sql = "update employee set age = age + 1 where id = " + str(myId)
    try:
        curses.execute(sql)
        db.commit()
    except:
        db.rollback()
        print("跟新数据失败")
    db.close()

def delEmp():
    db = pymysql.connect(host="localhost", user="root", password="root", database="sys")
    curses = db.cursor()
    sql = "delete from employee where age = %s" % (13)
    try:
        curses.execute(sql)
        db.commit()
    except:
        db.rollback()
    db.close()